<?php
/**
 * Criteria
 * - Object helps to create a SQL
 * @author Do Truong Giang
 * @since 1.0 - Feb 2, 2008
 * @copyright Copyright &copy; 2009, Runsystem
 */
class Criteria_mysql {
	public $values;
	public $columns;
	private $conditions;
	private $tableNames;
	private $joins;
	private $joinTables;
	private $limit;
	private $havings;
	private $groups;
	private $orders;
	private $distinct;

	/**
	 * Constructor
	 */
	public function Criteria_mysql() {
		$this->values = array();
		$this->columns = array();
		$this->conditions = null;
		$this->tableNames = array();
		$this->joins = null;
		$this->joinTables = array();
		$this->limit = null;
		$this->havings = array();
		$this->groups = array();
		$this->orders = array();
		$this->distinct = false;
	}
	
	/**
	 * setTableNames
	 * - Set the array of tables. 
	 * - Used to create SELECT, INSERT, DELETE query
	 * @param mixed $table
	 * @since 1.0 - 29-04-2009
	 * @author Nguyen Thanh Quyen
	 */
	public function setTableNames($tables){
		if(!is_array($tables)){
			$this->tableNames[] = $tables;
		}
		else{
			$this->tableNames = $tables;
		}
	}
	
	/**
	 * setColumns
	 * - Set the array of columns
	 * - Used to create UPDATE, INSERT query
	 * @param array $columns
	 * @since 1.0 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function setColumns($columns){
		if(!is_array($columns)){
			$this->columns[] = $columns;
		}
		else{
			$this->columns = $columns;
		}
	}
	
	/**
	 * setValues
	 * - Set the array of values
	 * - Used to create UPDATE, INSERT query
	 * @param array $values
	 * @since 1.0 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function setValues($values){
		if(!is_array($values)){
			$this->values[] = $values;
		}
		else{
			$this->values = $values;
		}
	}
	/**
	 * setOrders
	 * - Set the orders array
	 * @param Array $orders
	 * @author Nguyen Thanh Quyen
	 * @since 1.0 - 28-07-2009
	 */
	public function setOrders($orders) {
		if (!is_array($orders)) {
			$this->orders[] = $orders;
		}
		else {
			$this->orders = $orders;
		}
	
	}	
	/**
	 * setDistinct
	 * - Set the boolean distinct
	 * - Used to create SELECT query
	 * @param Boolean $distinct
	 * @since 1.0 - 28-04-2009 
	 * @author Nguyen Thanh Quyen
	 */
	public function setDistinct(){
		$this->distinct = true;
	}
	/**
	 * setCondition1
	 * - set condition for all query
	 * can use to join two table 
	 * @param String $columnName : the name of column
	 * @param Mixed $values
	 * @param String $operator
	 * @param Boolean $flag
	 * @since 1.0 - Aug 19 2009
	 * @author Nguyen Khac Tu
	 */
	public function setCondition1($columnName, $values, $operator, $flag = null) {
		$columnName = mysql_real_escape_string($columnName);
		$operator = strtolower($operator);
		$temp = array();
		
		// Check the case operator as "in"
		if ($operator == "in") {
			try {
				if (!is_array($values)) {
					throw new MyException('The second parameter must be an array!');
				}
				$count = count($values);
				for($i = 0; $i < $count; $i++){
					$value = addcslashes($values[$i], '\\');
					$temp[$i] = $value ;
				}
				$condition = $columnName . " " . $operator . " (" . implode(',',$temp).")";
			}
			catch (MyException $me) {
				echo "An exception occurs :" . $me->getMessage();
			}
		}
		else {
			if (!is_array($values)) {
				if ($operator == "like") {
					$values = "'%" . mysql_real_escape_string($values) . "%'";
					$condition = $columnName . " " . $operator . $values;
				} 
				else {
					$values =  mysql_real_escape_string($values);
					$condition = $columnName . $operator . $values;
				}
			}
			// Develop for keys array in BaseProcess.class
			else {
				$n = count($values);
				for($i = 0; $i < $n; $i++){
					$value = addcslashes($values[$i], '\\');
					if ($i == 0){
						$condition = $columnName . $operator . $value  ;
					}
					else{
						$condition .= " AND " . $columnName . $operator . $value ;
					}
				}
			}
		}
		// Set condition
		if ($this->conditions === null) {
			$this->conditions = $condition;
		}
		else {
			$this->conditions .= $flag . $condition;
		}
	}
	/**
	 * addAnd
	 * - Add a AND condition
	 * @param String $columnName
	 * @param Mixed $values
	 * @param String $operator
	 * @version 1.0 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function addAnd1($columnName, $values, $operator) {
		$flag = " AND ";
		$this->setCondition1($columnName, $values, $operator, $flag);
	}
	/**
	 * setCondition
	 * - set condition for all query
	 * @param String $columnName : the name of column
	 * @param Mixed $values
	 * @param String $operator
	 * @param Boolean $flag
	 * @since 1.0 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function setCondition($columnName, $values, $operator, $flag = null) {
		$columnName = mysql_real_escape_string($columnName);
		$operator = strtolower($operator);
		$temp = array();
		
		// Check the case operator as "in"
		if ($operator == "in") {
			try {
				if (!is_array($values)) {
					throw new MyException('The second parameter must be an array!');
				}
				$count = count($values);
				for($i = 0; $i < $count; $i++){
					$value = addcslashes($values[$i], '\\');
					$temp[$i] = "'" . $value . "'";
				}
				$condition = $columnName . " " . $operator . " (" . implode(',',$temp).")";
			}
			catch (MyException $me) {
				echo "An exception occurs :" . $me->getMessage();
			}
		}
		else {
			if (!is_array($values)) {
				if ($operator == "like") {
					$values = "'%" . mysql_real_escape_string($values) . "%'";
					$condition = $columnName . " " . $operator . $values;
				} 
				else {
					$values = "'" . mysql_real_escape_string($values) . "'";
					$condition = $columnName . $operator . $values;
				}
			}
			// Develop for keys array in BaseProcess.class
			else {
				$n = count($values);
				for($i = 0; $i < $n; $i++){
					$value = addcslashes($values[$i], '\\');
					if ($i == 0){
						$condition = $columnName . $operator . "'" . $value . "'" ;
					}
					else{
						$condition .= " AND " . $columnName . $operator . "'" . $value . "'";
					}
				}
			}
		}
		// Set condition
		if ($this->conditions === null) {
			$this->conditions = $condition;
		}
		else {
			$this->conditions .= $flag . $condition;
		}
	}
	/**
	 * setLimit
	 * - Set the limitation of selected record
	 * - Used only for SELECT query
	 * @param Integer $start :Starting position
	 * @param Integer $length :Number of selected records
	 * @since 1.0 - Feb 2, 2008
	 */
	public function setLimit($start, $length) {
		$this->limit = " LIMIT $start, $length ";
	}
	
	/**
	 * addSelectColumn
	 * - Add a column for select to this criteria
	 * @param String $column A column in a table, the format of $column is: tablename.column
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addSelectColumn($column) {
		$this->columns[] = $column;
		// Extract the table name if it has format tablename.fieldname
		if (preg_match("/^[a-zA-Z0-9_]+\.[a-zA-Z0-9_\*]+$/", $column)) {
			list ($table) = explode(".", $column);
			$this->tableNames[] = $table;
		}
	}
	
	/**
	 * addJoin
	 * - Create the join part, add: JOIN tablename2 ON (tablename1.fieldname1=tablename2.fieldname2)
	 * @param String $joinField1 Having format tablename1.fieldname1
	 * @param String $joinField2 Having format tablename2.fieldname2
	 * @param String $joinType Either INNER JOIN or LEFT JOIN
	 */
	public function addJoin($joinField1, $joinField2, $joinType) {
		list ($table2) = explode(".", $joinField2);
		list ($table1) = explode(".", $joinField1);
		$this->joins .= " " . $joinType . " " . $table2 . " ON ($joinField1=$joinField2)";
		// Add table1 to list of selected tables
		$this->tableNames[] = $table1;
		// Add table2 to join tables. We will remove joins table from list of table when create SQL
		$this->joinTables[] = $table2;
	}
	
	/**
	 * addAnd
	 * - Add a AND condition
	 * @param String $columnName
	 * @param Mixed $values
	 * @param String $operator
	 * @version 1.0 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function addAnd($columnName, $values, $operator) {
		$flag = " AND ";
		$this->setCondition($columnName, $values, $operator, $flag);
	}
	
	/**
	 * addOr
	 * - Add an OR condition
	 * @param String $columnName
	 * @param Mixed $values
	 * @param String $operator
	 * @since 1.0 - Feb 11, 2008
	 * @version 1.1 - April 27 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function addOr($columnName, $values, $operator) {
		$flag = " OR ";
		$this->setCondition($columnName, $values, $operator, $flag);
	}
	
	/**
	 * addXor
	 * - Add an XOR condition
	 * @param String $columnName
	 * @param Mixed $values
	 * @param String $operator
	 * @since 1.0 - 28-04-2009
	 * @author Nguyen Thanh Quyen
	 */
	public function addXor($columnName, $values, $operator) {
		$flag = " XOR ";
		$this->setCondition($columnName, $values, $operator, $flag);
	}
	
	/**
	 * addAndCriteria
	 * - Add AND with other criteria
	 * @param Criteria $c
 	 * @since 1.0 - Feb 2, 2008
	 */
	public function addAndCriteria($c) {
		// Add the condition of $c to this condition
		if ($this->conditions === null) {
			$this->conditions .= "(" .$c->getConditions() . ")";
		}
		else {
			$this->conditions .= " AND (" . $c->getConditions() . ")";
		}
	}
	
	/**
	 * addOrCriteria
	 * - Add OR with other criteria
	 * @param Criteria $c
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addOrCriteria($c) {
		// Add the condition of $c to this condition
		if ($this->conditions === null) {
			$this->conditions .= "(" .$c->getConditions() . ")";
		}
		else {
			$this->conditions .= " OR (" . $c->getConditions() . ")";
		}
	}
	
	/**
	 * addXorCriteria
	 * - Add XOR with other criteria
	 * @param Criteria $c
	 * @since 1.0 - 28-04-2009
	 * @author Nguyen Thanh Quyen
	 */
	public function addXorCriteria($c) {
		// Add the condition of $c to this condition
		if ($this->conditions === null) {
			$this->conditions .= "(" .$c->getConditions() . ")";
		}
		else {
			$this->conditions .= " XOR (" . $c->getConditions() . ")";
		}
	}
	
	/**
	 * addAscendingOrderByColumn
	 * - Create the ORDER BY column ASC
	 * - Used only for SELECT query
	 * @param String $column The ascending column name
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addAscendingOrderByColumn($column) {
		$this->orders[] = $column . " ASC ";
	}
	
	/**
	 * addDescendingOrderByColumn
	 * - Cretae ORDER BY column DESC
	 * - Used only for SELECT query
	 * @param String $column The descending column name
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addDescendingOrderByColumn($column) {
		$this->orders[] = $column . " DESC ";
	}
	
	/**
	 * addHaving
	 * - Support for having statement
	 * - Will create HAVING statement
	 * - Used only for SELECT query
	 * @param String $statement The having statement
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addHaving($statement) {
		$this->havings[] = $statement;
	}
	
	/**
	 * addGroupByColumn
	 * - Create GROUP BY column
	 * - Used only for SELECT query
	 * @param String $column The group column name
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 2, 2008
	 */
	public function addGroupByColumn($column) {
		$this->groups[] = $column;
	}
	
	/**
	 * getTableNames
	 * @return Array The array of table names
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 2, 2008
	 */
	public function getTableNames() {
		return $this->tableNames;
	}
	
	/**
	 * getConditions
	 * - Return the condition part of a query
	 * @author Do Truong Giang
	 * @since 1.0 - Feb 11, 2008
	 * @return String
	 */
	public function getConditions() {
		return $this->conditions;
	}

	/**
	 * getSelectSql
	 * - Create a form SELECT query
	 * @return The SELECT query
	 * @since 1.0 - Feb 2, 2008
	 * @version 1.1 - April 27 2009
	 */
	public function getSelectSql() {
		// Remove joins table from $tableNames
		$this->tableNames = array_diff($this->tableNames, $this->joinTables);
		$this->tableNames = array_unique($this->tableNames);
		$distinct = null;

		if ($this->distinct) {
			$distinct = "DISTINCT ";
		}
		// Create SELECT part
		if (empty($this->columns)) {
			$sql = "SELECT " . $distinct . "*";
		}
		else {
			$sql = "SELECT " . $distinct . implode(",", $this->columns);
		}
		// Create FROM statment
		$sql .= " FROM " . implode(",", $this->tableNames);
		// Create JOINS
		$sql .= $this->joins;
		// Create WHERE
		if ($this->conditions != null) {
			$sql .= " WHERE " . $this->conditions;
		}
		// Create GROUP
		if (!empty($this->groups)) {
			$sql .= " GROUP BY " . implode(",", $this->groups);
		}
		// Create HAVING
		if (!empty($this->havings)) {
			$sql .= " HAVING " . implode(",", $this->havings);
		}
		// Create ORDER
		if (!empty($this->orders)) {
			$sql .= " ORDER BY " . implode(",", $this->orders);
		}
		// Create LIMIT
		if ($this->limit != null) {
			$sql .= $this->limit;
		}
		return $sql;
	}

	/**
	 * getInsertSql
	 * - Create a form INSERT query
	 * @return The INSERT query
	 * @since 1.0 - April 24, 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function getInsertSql() {
		// Create INSERT part
		$sql = "INSERT INTO " . implode(',', $this->tableNames);
		if (!empty($this->columns)) {
			$sql .= "(" . implode(',', $this->columns) . ")";
		}
		// Create VALUES
		if (!empty($this->values)) {
			$count = count($this->values);
			$temp = array();
			for($i = 0; $i < $count; $i++){
				$value = addcslashes($this->values[$i], '\\');
				$temp[$i] = "'" . $value . "'";
			}
			$sql .= " VALUES(" . implode(',', $temp) . ")";
		}
		return $sql;
	}

	/**
	 * getUpdateSql
	 * @return The UPDATE query
	 * - Create a form INSERT
	 * @since 1.0 - April 27, 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function getUpdateSql() {
		$n = count($this->columns);
		// Create UPDATE part
		$sql = "UPDATE " . implode(',', $this->tableNames);
		// Create SET
		$temp = array ();
		for($i = 0; $i < $n; $i++) {
			$value = addcslashes($this->values[$i], '\\');
			$temp[$i] = $this->columns[$i] . "=" . "'" . $value ."'";
		}
		$sql .= " SET " . implode(',', $temp);
		// Create WHERE
		if ($this->conditions != null) {
			$sql .= " WHERE " . $this->conditions;
		}
		return $sql;
	}

	/**
	 * getDeleteSql
	 * @return The Delete query
	 * @since 1.0 - April 27, 2009
	 * @author Nguyen Thanh Quyen
	 */
	public function getDeleteSql() {
		// Create DELETE part
		$sql = "DELETE FROM " . implode(',', $this->tableNames);
		// Create WHERE
		if ($this->conditions != null) {
			$sql .= " WHERE " . $this->conditions;
		}
		return $sql;
	}
}
?>